<?php
	class WxUserModel extends CI_Model {
		// 表名
		static $wxUserTable = "wx_user";
		static $wxUserBindTable = "wx_user_bind";
		static $wxUserReadTable = "wx_user_read";
		static $wxNoteTable = "wx_note";
		static $wxBrandBroadcastTable = "wx_brand_broadcast";
		static $wxUserHealthTable = "wx_user_health";
		static $studentTable = 'school_student';
		static $wxUserHealthWarnTable = "wx_user_health_warn";
		static $chargeTable = "school_charge";
		static $chargePayTable = "school_charge_pay";
		static $classTable = 'school_class';
		static $wxTemplateProcessTable = 'wx_template_process';

	    public function __construct() {
	        parent::__construct();
	    }

	    // 通过id获取
	    public function get_wx_user_by_id($id) {
	    	$this->db->where("id", $id);
	    	return $this->db->get(self::$wxUserTable)->row_array();
        }

        // 通过openid获取
	    public function get_wx_user_by_openid($openid) {
	    	$this->db->where("openid", $openid);
	    	return $this->db->get(self::$wxUserTable)->row_array();
		}
		
		// 获取微信用户列表
		public function get_wx_user_list($params=array()) {
			if(isset($params['idArr'])) {
				$this->db->where_in('id', $params['idArr']);
			}
			if(isset($params['openidArr'])) {
				$this->db->where_in('openid', $params['openidArr']);
			}
			if(isset($params['emptyInfo'])) {
				$this->db->where("`nickname` = '' OR `headimgurl` = ''");
			}
			if(isset($params['createtime'])) {
				$this->db->where("`createtime` > '{$params['createtime']}'");
			}
			return $this->db->get(self::$wxUserTable)->result_array();
		}

        // 获取已绑定的账号列表
        public function get_bind_user_list($params=array()) {
			if(isset($params['wxUserId'])) {
				$this->db->where('wx_user_id', $params['wxUserId']);
			}
			if(isset($params['bindUserId'])) {
				$this->db->where('bind_user_id', $params['bindUserId']);
			}
			// 用户id集合
			if(isset($params['bindUserIdArr'])) {
				$this->db->where_in('bind_user_id', $params['bindUserIdArr']);
			}
			if(isset($params['bindUserType'])) {
				$this->db->where('bind_user_type', $params['bindUserType']);
			}
			if(isset($params['isDelete'])) {
				$this->db->where('is_delete', $params['isDelete']);
			}
			// 排序
			if(isset($params['orderBy'])) {
				$this->db->order_by($params['orderBy']);
			}
            return $this->db->get(self::$wxUserBindTable)->result_array();
		}
		
		// 获取已绑定的账号
		public function get_bind_user_info($wxUserId, $bindUserId, $bindUserType) {
			$this->db->where('wx_user_id', $wxUserId);
			$this->db->where('bind_user_id', $bindUserId);
			$this->db->where('bind_user_type', $bindUserType);
			$this->db->where('is_delete', 0);
			return $this->db->get(self::$wxUserBindTable)->row_array();
		}

		// 获取已读列表
		public function get_wx_user_read_list($params) {
			if(isset($params['schoolId'])) {
				$this->db->where('school_id', $params['schoolId']);
			}
			if(isset($params['userId'])) {
				$this->db->where('user_id', $params['userId']);
			}
			if(isset($params['bindUserType'])) {
				$this->db->where('bind_user_type', $params['bindUserType']);	
			}
			if(isset($params['itemId'])) {
				$this->db->where('item_id', $params['itemId']);	
			}
			if(isset($params['itemType'])) {
				$this->db->where('item_type', $params['itemType']);	
			}

	    	// 是否只获取条数
	    	if(isset($params['getRowNumber'])) return $this->db->count_all_results(self::$wxUserReadTable);	
	    	return $this->db->get(self::$wxUserReadTable)->result_array();
		}

		// // 获取留言列表
		// public function get_wx_note_list($params) {
		// 	if(isset($params['sendUserId'])) {
		// 		$this->db->where('send_user_id', $params['sendUserId']);
		// 	}
		// 	if(isset($params['sendUserType'])) {
		// 		$this->db->where('send_user_type', $params['sendUserType']);
		// 	}
		// 	if(isset($params['receiveUserId'])) {
		// 		$this->db->where('receive_user_id', $params['receiveUserId']);
		// 	}
		// 	if(isset($params['receiveUserType'])) {
		// 		$this->db->where('receive_user_type', $params['receiveUserType']);
		// 	}
		// 	if(isset($params['isBrand'])) {
		// 		$this->db->where('is_brand', $params['isBrand']);
		// 	}
		// 	return $this->db->get(self::$wxNoteTable)->result_array();
		// }

		// 获取用户留言列表
		public function get_user_wx_note_list($userId, $bindUserType, $isBrand, $currentWeek = false) {
			// 筛选7天内的时间
			if($currentWeek) {
				$weekBeforeDatetime = date('Y-m-d H:i:s', strtotime("-1 week"));
				$this->db->where("`send_user_id`='{$userId}' AND `send_user_type`='{$bindUserType}' AND `is_brand`='{$isBrand}' AND `createtime`>='{$weekBeforeDatetime}'");
				$this->db->or_where("`receive_user_id`='{$userId}' AND `receive_user_type`='{$bindUserType}' AND `is_brand`='{$isBrand}' AND `createtime`>='{$weekBeforeDatetime}'");
			} else {
				$this->db->where("`send_user_id`='{$userId}' AND `send_user_type`='{$bindUserType}' AND `is_brand`='{$isBrand}'");
				$this->db->or_where("`receive_user_id`='{$userId}' AND `receive_user_type`='{$bindUserType}' AND `is_brand`='{$isBrand}'");
			}
		
			// 获取5000条最新记录
			$this->db->order_by('createtime DESC, id DESC');
			// $this->db->limit(5000, 0);
			// 跟班牌同步，只获取50条
			$this->db->limit(WX_NOTE_PAGE_SIZE, 0);
			return $this->db->get(self::$wxNoteTable)->result_array();
		}

		// 获取留言对话框
		public function get_wx_note_dialog($userId, $userType, $otherUserId, $otherUserType, $isBrand, $page = 1) {
			$this->db->where("`send_user_id`='{$userId}' AND `send_user_type`='{$userType}' AND `receive_user_id`='{$otherUserId}' AND 
				`receive_user_type`='{$otherUserType}' AND `is_brand`='{$isBrand}'");
			$this->db->or_where("`send_user_id`='{$otherUserId}' AND `send_user_type`='{$otherUserType}' AND `receive_user_id`='{$userId}' AND 
				`receive_user_type`='{$userType}' AND `is_brand`='{$isBrand}'");
			$this->db->order_by('createtime DESC, id DESC');
			// 每页默认50条
			$this->db->limit(WX_NOTE_PAGE_SIZE, WX_NOTE_PAGE_SIZE * ($page - 1));
			return $this->db->get(self::$wxNoteTable)->result_array();
		}

		// 获取留言详情
		public function get_wx_note_info($id) {
			$this->db->where('id', $id);
			return $this->db->get(self::$wxNoteTable)->row_array();
		}

		// 获取用户留言，通过发送人和接收人
		public function get_wx_note_info_by_user($sendUserId, $bindUserType, $receiveUserId, $isBrand) {
			$this->db->where("`send_user_id`='{$sendUserId}' AND `send_user_type`='{$bindUserType}' AND `receive_user_id`='{$receiveUserId}' AND `is_brand`='{$isBrand}'");
			return $this->db->get(self::$wxNoteTable)->row_array();
		}

		// 获取广播列表
		public function get_wx_brand_broadcast_list($params) {
			// 分页
			if(isset($params['pageStart']) && isset($params['pageSize'])) $this->db->limit($params['pageSize'], $params['pageStart']);
			// 读取指定列
			if(isset($params['field'])) $this->db->select($params['field']);
			if(isset($params['userId'])) {
				$this->db->where('user_id', $params['userId']);
			}
			if(isset($params['bindUserType'])) {
				$this->db->where('bind_user_type', $params['bindUserType']);
			}
			if(isset($params['receiveStudentId'])) {
				$this->db->where('receive_student_id', $params['receiveStudentId']);
			}
			if(isset($params['isDelete'])) {
				$this->db->where('is_delete', $params['isDelete']);
			}
			if(isset($params['orderBy'])) {
				$this->db->order_by($params['orderBy']['order'], $params['orderBy']['sort']);
			} else {
				// 默认按更新时间
				$this->db->order_by('updatetime', 'DESC');
			}
			return $this->db->get(self::$wxBrandBroadcastTable)->result_array();
		}

		// 获取广播详情
		public function get_wx_brand_broadcast_info($id) {
			$this->db->where('id', $id);
			return $this->db->get(self::$wxBrandBroadcastTable)->row_array();
		}

		// 获取健康详情
		public function get_wx_user_health_list($params) {
			$healthTable = TABLE_PREFIX . self::$wxUserHealthTable;
			$studentTable = TABLE_PREFIX . self::$studentTable;
			$sql = '';
			$selectSql = '';
			if(isset($params['join_student'])) {
				$sql .= " LEFT JOIN {$studentTable} ON {$studentTable}.id =  {$healthTable}.user_id";
				$selectSql = ", {$studentTable}.headimgurl";
				// $this->db->join(self::$studentTable, self::$studentTable.".id = ".self::$wxUserHealthTable. ".user_id" , 'left');
			}
			$sql .= " WHERE 1 ";
			if(isset($params['schoolId'])) {
				$sql .= " AND " . $healthTable . ".school_id = " . $params['schoolId'];
				// $this->db->where(self::$wxUserHealthTable .'.school_id', $params['schoolId']);
			}
			if(isset($params['bindusertype'])) {
				$sql .= " AND " . $healthTable . ".bind_user_type = " . $params['bindusertype'];
				// $this->db->where(self::$wxUserHealthTable .'.bind_user_type', $params['bindusertype']);
			}
			// 传班级id，就不用处理年级id
			if(isset($params['classId'])) {
				$sql .= " AND FIND_IN_SET('{$params['classId']}', {$healthTable}.class_ids)";
				// $this->db->where("FIND_IN_SET('{$params['classId']}', `class_ids`)");
			} else {
				if(isset($params['classSet'])) {
					if($params['classSet'] === 0 || $params['classSet'] === '0') {
						// 年级不存在班级
						$sql .= ' AND 0';
						// $this->db->where('id', 0);
					} else {
						// var_export($params['classSet']);exit;
						$classIdArr = explode(',', $params['classSet']);
	
						$str = ' AND (0';
						foreach($classIdArr as $classId) {
							$str .= " OR FIND_IN_SET('{$classId}', `class_ids`)";
						}
						$str .= ' )';
						$sql .= $str;
						// $this->db->where($str);
					}
				}
			}

			if(isset($params['keyword'])) {
				$sql .= " AND " . $healthTable . ".realname LIKE '%" . $params['keyword'] . "%'";
				// $this->db->like(self::$wxUserHealthTable .'.realname', $params['keyword']);
			}
			if(isset($params['current_status'])) {
				$sql .= " AND " . $healthTable . ".current_status = " . $params['current_status'];
				// $this->db->where('current_status', $params['current_status']);
			}
			if(isset($params['family_status'])) {
				$sql .= " AND " . $healthTable . ".family_status = " . $params['family_status'];
				// $this->db->where('family_status', $params['family_status']);
			}
			if(isset($params['symptom'])) {
				$sql .= " AND " . $healthTable . ".symptom = " . $params['symptom'];
				// $this->db->where("FIND_IN_SET('{$params['symptom']}', `symptom`)");
			}
			if(isset($params['treatment'])) {
				$sql .= " AND " . $healthTable . ".treatment = " . $params['treatment'];
				// $this->db->where('treatment', $params['treatment']);
			}
			if(isset($params['is_contact'])) {
				$sql .= " AND " . $healthTable . ".is_contact = " . $params['is_contact'];
				// $this->db->where('is_contact', $params['is_contact']);
			}
			if(isset($params['is_return'])) {
				$sql .= " AND " . $healthTable . ".is_return = " . $params['is_return'];
				// $this->db->where('is_return', $params['is_return']);
			}
			if(isset($params['is_detained'])) {
				$sql .= " AND " . $healthTable . ".is_detained = " . $params['is_detained'];
				// $this->db->where('is_detained', $params['is_detained']);
			}
			// if(isset($params['begin_time'])) {
			// 	$this->db->where(self::$wxUserHealthTable . '.createtime >=', $params['begin_time']);
			// }
			// if(isset($params['end_time'])) {
			// 	$this->db->where(self::$wxUserHealthTable . '.createtime <=', $params['end_time']);
			// }
			
			if(isset($params['createdate'])) {
				// 传日期就只获取当天最新一条
				$beginTime = $params['createdate'] . ' 00:00:00';
				$endTime = $params['createdate'] . ' 23:59:59';
				$sql .= " AND {$healthTable}.createtime >= '{$beginTime}' AND {$healthTable}.createtime <= '{$endTime}'";
				// $this->db->where(self::$wxUserHealthTable . '.createtime >=', $beginTime);
				// $this->db->where(self::$wxUserHealthTable . '.createtime <=', $endTime);
			}

			if(isset($params['enddate'])) {
				// 获取截止日期最新一条
				// 传日期就只获取当天最新一条
				$endTime = $params['enddate'] . ' 23:59:59';
				$sql .= " AND {$healthTable}.createtime <= '{$endTime}'";
			}

			$whereSql = "SELECT {$healthTable}.*" . $selectSql .  " FROM " . $healthTable . $sql . " ORDER BY " . $healthTable . ".createtime DESC LIMIT 10000";
			// $whereSql = "SELECT {$healthTable}.id AS id, * FROM " . $healthTable . $sql . " ORDER BY " . $healthTable . ".createtime DESC LIMIT 10000";
			// 分页
			if(isset($params['pageStart']) && isset($params['pageSize'])) {
				$limitSql = 'LIMIT ' . $params['pageStart']. ',' . $params['pageSize'];
			} else {
				$limitSql = '';
			}
			$mainSql = "SELECT tt.* FROM ( " . $whereSql . ") AS tt GROUP BY user_id,bind_user_type ORDER BY " . "tt.createtime DESC " . $limitSql;
			$query = $this->db->query($mainSql);
	        $healthList = $query->result_array();
			if(isset($params['getRowNumber'])) {
	    		return count($healthList);
	    	} else {
				return $healthList;
			}	
		}

		// 获取健康详情
		public function get_wx_user_health_info_by_user_id($userId, $bindUserType) {
			$this->db->where('user_id', $userId);
			$this->db->where('bind_user_type', $bindUserType);
			return $this->db->get(self::$wxUserHealthTable)->row_array();

		}
		// 获取健康详情
		public function get_wx_user_health_info($id) {
			$this->db->where('id', $id);
			return $this->db->get(self::$wxUserHealthTable)->row_array();
		}

		// 获取健康预警列表
		public function get_wx_user_health_warn_list($params) {
			if(isset($params['schoolId'])) {
				$this->db->where('school_id', $params['schoolId']);
			}
			$this->db->where('is_delete', 0);
			return $this->db->get(self::$wxUserHealthWarnTable)->result_array();
		}
		// 获取健康预警详情
		public function get_wx_user_health_warn_info($id) {
			$this->db->where('id', $id);
			return $this->db->get(self::$wxUserHealthWarnTable)->row_array();
		}
		// 获取健康预警详情，通过key
		public function get_wx_user_health_warn_info_by_key($schoolId, $key) {
			$this->db->where('school_id', $schoolId);
			$this->db->where('key', $key);
			$this->db->where('is_delete', 0);
			return $this->db->get(self::$wxUserHealthWarnTable)->row_array();
		}

	    // 获取支付列表
	    public function get_charge_pay_list($params){
            $chargePayTable = TABLE_PREFIX . self::$chargePayTable;
			$chargeTable = TABLE_PREFIX . self::$chargeTable;
			$studentTable = TABLE_PREFIX . self::$studentTable;
			$classTable = TABLE_PREFIX . self::$classTable;
			$where = " LEFT JOIN {$chargeTable} ON {$chargeTable}.id = {$chargePayTable}.charge_id LEFT JOIN {$studentTable} ON {$studentTable}.id = {$chargePayTable}.student_id
			LEFT JOIN {$classTable} ON {$classTable}.id = {$studentTable}.class_id ";
			$where .= " WHERE 1 ";
	    	// 学校id
			if(isset($params['schoolId'])) $where .= " AND {$chargePayTable}.`school_id`='{$params['schoolId']}'";
			// 是否已支付
			if(isset($params['isPaid'])) $where .= " AND {$chargePayTable}.`is_paid` = '{$params['isPaid']}'";
			// 传班级id，就不用处理年级id
			if(isset($params['classId'])) {
				$where .= " AND {$classTable}.`id`='{$params['classId']}'";
			} else {
				if(isset($params['classSet'])) {
					if($params['classSet'] === 0 || $params['classSet'] === '0') {
						// 年级不存在班级
						$where .= ' AND 0';
					} else {
						$classIdArr = explode(',', $params['classSet']);
						$str = ' AND (0';
						foreach($classIdArr as $classId) {
							$str .= " OR  {$classTable}.`id` = '{$classId}'";
						}
						$str .= ' )';
						$where .= $str;
					}
				}
			}
			if(isset($params['begindate']) && $params['begindate']) {
				$begintime = $params['begindate'] . ' 00:00:00';
				$where .= " AND {$chargePayTable}.`paytime` >= '{$begintime}'";
			}
			if(isset($params['enddate']) && $params['enddate']) {
				$endtime = $params['enddate'] . ' 23:59:59';
				$where .= " AND {$chargePayTable}.`paytime` <= '{$endtime}'";
			}
			// 激活途径
			if(isset($params['way'])) $where .= " AND {$chargePayTable}.`pay_way` = '{$params['way']}'";
			// 学生姓名
			if(isset($params['keyword']) && $params['keyword']) {
				$where .= " AND {$studentTable}.`realname` LIKE '%{$params['keyword']}%'";
			}
			// 排序
			if(isset($params['orderBy'])) {
				$orderSql = ' ORDER BY ' . $params['orderBy'];
			} else {
				$orderSql = ' ORDER BY paytime DESC';
			}
			// 分页
			if(isset($params['pageStart']) && isset($params['pageSize'])) {
				$limitSql = ' LIMIT ' . $params['pageStart']. ',' . $params['pageSize'];
			} else {
				$limitSql = '';
			}
			$mainSql = "SELECT {$chargePayTable}.*, {$chargeTable}.name, {$chargeTable}.active_mode, {$chargeTable}.active_month, {$chargeTable}.active_time, {$studentTable}.realname, {$studentTable}.username, {$classTable}.class_name FROM {$chargePayTable}" 
			. $where . $orderSql . $limitSql;
			$query = $this->db->query($mainSql);
			$payList = $query->result_array();
			if(isset($params['getRowNumber'])) {
				return count($payList);
			} else {
				return $payList;
			}
			return $this->db->get(self::$chargePayTable)->result_array();
		}

		// 获取收费项信息，通过订单号
		public function get_charge_pay_info_by_trade_no($tradeNo){
			$where = "`out_trade_no`='{$tradeNo}'";
			$chargeInfo = $this->db->where($where)->get(self::$chargePayTable)->row_array();
			return $chargeInfo;
		}
		
		public function get_template_process_list(){
			$this->db->where('is_delete', 0);
			$processList = $this->db->get(self::$wxTemplateProcessTable)->result_array();
			return $processList;
		}

		public function get_template_process(){
			$this->db->where('is_delete', 0);
			$processList = $this->db->get(self::$wxTemplateProcessTable)->row_array();
			return $processList;
		}
    }